<?php
class Db_product extends MVC_SQL {

    public function __construct() {
        $this->setTable('ams_product');

    }
    /**
     *****************Edit by Nam*****************
     */
    public function getOMoT_ShowEditPage_Defaul($product_id) {
        $sql ="SELECT * FROM ams_other_transport_method_defaul WHERE other_product_id = '1' AND 	other_title_of_method <> '' ";
        return $this->getRecordsetExtend($sql);
    }
    public function updateOMoT_defaul($product_id,$aData) {
    //Step 1 : delete all other method exist
        $product_id='1';
        $sqlDel = "DELETE FROM ams_other_transport_method_defaul WHERE other_product_id= '{$product_id}' " ;
        if ($this->db_query($sqlDel)) {
        //Step 2 : Insert 10 record to db
            if (is_array($aData))
                foreach ($aData as $recordItem) {
                    $sqlIns = "INSERT INTO ams_other_transport_method_defaul(other_product_id,other_title_of_method,
															other_link_to_price_list,other_uniform_price) 
							VALUES('{$recordItem['other_product_id']}',
									'{$recordItem['other_title_of_method']}','{$recordItem['other_link_to_price_list']}',
									'{$recordItem['other_uniform_price']}')" ;
                    //				echo $sqlIns."<br>";die();
                    $check_i = $this->db_query($sqlIns);
                    if (!$check_i)
                        return false;
                }
        }
    }
    public function updateProductDefaul($aData_post,$aData_post_name) {
        $sql='';
        $sql  = " UPDATE ams_product_defaul SET ";
        $count = count($aData_post_name);
        for ($i = 0; $i < $count; $i++) {
        //if(($aData_post[$i]  != "" ) && ($i != 1)){
            $sql1 = " {$aData_post_name[$i]} = '{$aData_post[$i]}' ,";
            $sql .=$sql1;
        //}else{
        //$sql1 = " {$aData_post_name[$i]} = '{$aData_post[$i]}' ,";
        //$sql .=$sql1;
        //}
        }

        $sql=substr($sql,1,strlen($sql)-2);
        $sql .= " WHERE product_id = '1' ";
        $check= $this->db_query($sql);
        if (!$check) return false;
        return true;
    //return $sql;
    }
    public function getInfoProductDefaul($product_id) {
        $this->setTable('ams_product_defaul');
        return $this->getRecordsetByPKD($product_id);
    //$sql ="SELECT * FROM ams_produc_defaul WHERE product_id_defaul = '1'";
    //return $this->getRecordsetExtend($sql);
    }
    public function getImageProduct_OBJ($product_id,$xID) {
        $sSql	= "SELECT product_picture{$xID} FROM ams_product ";
        $sSql	.= "WHERE product_id = '{$product_id}'";
        return $this->getRecordsetExtend($sSql);
    //return $sSql;
    }

    /**
     ***********************************
     */
    public function getListProduct($oModel) {
        $oModel->oNavigator = new paging();
        $page_size = $oModel->getData('page_size');
        $pagegroup_size = $oModel->getData('pagegroup_size');
        $stylesheet = $oModel->getData('stylesheet');

        $sSql	= "SELECT * FROM ams_product ";
        $sSql	.= "ORDER BY SUBSTRING( product_id, 1, 1 ) , CAST( SUBSTRING( product_id, 2 ) AS UNSIGNED ) ASC";
        $total_record = $this->getNumRow($sSql);
        $oModel->oNavigator->init($sSql, $total_record, $page_size, $pagegroup_size, 'pUser', $stylesheet);
        $sSql = $oModel->oNavigator->getSql();
        return $this->getRecordsetExtend($sSql);
    }

    public function getListProvince($oModel) {
        $sql = "SELECT * FROM ams_province ORDER BY province_sort ASC";
        return  $this->getRecordsetExtend($sql);
    }
    public function deleteImageOfProduct($product_id) {
        $sql = "DELETE FROM ams_pictures WHERE pictures_product_id ='{$product_id}' ";
        return  $this->db_query($sql);
    }

    public function updateProductStatus($aData) {
        return $this->doUpdateByPK($aData);
    }

    public function getInfoProduct($product_id) {
        return $this->getRecordsetByPK($product_id);
    }
    public function filterProduct($oModel) {
        $oModel->oNavigator = new paging();
        $page_size = $oModel->getData('page_size');
        $pagegroup_size = $oModel->getData('pagegroup_size');
        $stylesheet = $oModel->getData('stylesheet');

        $fromDate = str_replace('/','-',$oModel->getData('fromDate') );
        $toDate = str_replace('/','-',$oModel->getData('toDate'));
        /**
         * ADD BY MINH FILTER BY CATEGORY
         */
        $category=$oModel->getData('category');
        $sFilterCat="";

        if(is_numeric($category)) {
            $sFilterCat = " AND product_category_id = {$category}";
        }
        else {
            $arr_cat_id=$this->getCategoryIdByName($category);
            if(is_array($arr_cat_id)) {
                $sFilterCat = " AND product_category_id in( ";
                $num=0;
                $lenght=count($arr_cat_id);
                foreach($arr_cat_id as $row) {
                    if($num==$lenght-1) {
                        $sFilterCat.= $row['product_category_yahoo_id'];
                    }
                    else {
                        $sFilterCat.= $row['product_category_yahoo_id'].',';
                    }
                    $num++;
                }
                $sFilterCat.= ")";
            }
        }


        if($fromDate!='' && $toDate!='') {
            $sFilterDate='';
            $sFilterDate.=	" AND ( date_format(p.product_date_added,'%Y-%m-%d') >= '{$fromDate}' ";
            $sFilterDate.=	" AND date_format(p.product_date_added,'%Y-%m-%d') <= '{$toDate}' ) ";
        }
        $sSql	 = "SELECT p.* FROM ams_product AS p  WHERE 1";
        $sSql	.=$sFilterDate;
        $sSql	.=$sFilterCat;
        $sSql	.= " ORDER BY SUBSTRING( product_id, 1, 1 ) , CAST( SUBSTRING( product_id, 2 ) AS UNSIGNED ) ASC ";
        $total_record = $this->getNumRow($sSql);
        $oModel->oNavigator->init($sSql, $total_record, $page_size, $pagegroup_size, 'pUser', $stylesheet);
        $sSql = $oModel->oNavigator->getSql();
        
        return $this->getRecordsetExtend($sSql);
    }

    /**
     *ADD BY MINHHTN FOR SEARCH BY CAT NAME
     */
    public function getCategoryIdByName($name) {
        $name=trim($name);
        //$name = mb_convert_encoding($name,"UTF-8","SJIS");
        $sSql="SELECT * FROM ams_product_category WHERE product_category_name like ' {$name} '";
        //echo $sSql;
        return $this->getRecordsetExtend($sSql);
    }
    public function getImage($pictures_product_id) {
        $sql = "SELECT product_pictures FROM ams_product WHERE product_id = '{$pictures_product_id}'";
        $getListIdImage =$this->getRecordsetExtend($sql);
        if (count($getListIdImage)==0) {
            return 'no found product id';
        } elseif (count($getListIdImage)>1) {
            return 'once more than one product id';
        }
        foreach ($getListIdImage as $data) {
            $array_id_iamge = explode(" ",$data['product_pictures']);
        }

        $sWhere	=	'';
        $xxx = count($array_id_iamge);
        $ZZZ =0;
        foreach ($array_id_iamge as $pictures_id) {
            $ZZZ++;
            $sWhere .= " (pictures_id = '{$pictures_id}')";
            if ($ZZZ<$xxx) {
                $sWhere .= " OR ";
            }
        }



        if ($sWhere!='') {


            $sql = "SELECT * FROM ams_pictures WHERE  ".$sWhere." LIMIT 3 ";
            return $this->getRecordsetExtend($sql);
        }

    }
    public function updateComment($aData_post,$arr_pictures_id) {
        foreach ($arr_pictures_id as $item) {
            $sql='';
            $sql  = " UPDATE ams_pictures SET ";
            $sql .= " pictures_comment = '{$aData_post[$item]}' ";
            $sql .= " WHERE pictures_id = '{$item}' ";
            $check= $this->db_query($sql);
            if (!$check) return false;
        }
        return true;
    }

    public function get_infor_pid($product_id) {
        $sql ="SELECT * FROM ams_product WHERE product_id = '{$product_id}' LIMIT 1";
        return $this->getRecordsetExtend($sql);
    }
    public function getOneProvince($product_province_id) {
        $sql ="SELECT province_name FROM ams_province WHERE province_code = '{$product_province_id}' LIMIT 1";
        $arrayName = $this->getRecordsetExtend($sql);
        if (is_array($arrayName)) {
            foreach ($arrayName as $name ) {
                return $name['province_name'];
            }

        } else  return '';
    }
    public function updateOMoT($product_id,$aData) {
    //Step 1 : delete all other method exist
        $sqlDel = "DELETE FROM ams_other_transport_method WHERE other_product_id= '{$product_id}' " ;
        if ($this->db_query($sqlDel)) {
        //Step 2 : Insert 10 record to db
            if (is_array($aData))
                foreach ($aData as $recordItem) {
                    $sqlIns = "INSERT INTO ams_other_transport_method(other_product_id,other_title_of_method,
															other_link_to_price_list,other_uniform_price) 
							VALUES('{$recordItem['other_product_id']}',
									'{$recordItem['other_title_of_method']}','{$recordItem['other_link_to_price_list']}',
									'{$recordItem['other_uniform_price']}')" ;
                    //				echo $sqlIns."<br>";die();
                    $check_i = $this->db_query($sqlIns);
                    if (!$check_i)
                        return false;
                }
        }
    }
    public function deleteOMoT($product_id) {
        $sqlDel = "DELETE FROM ams_other_transport_method WHERE other_product_id= '{$product_id}' " ;
        return $this->doUpdateExtend($sqlDel);
    }
    public function getOMoT($product_id) {
        $sql ="SELECT * FROM ams_other_transport_method WHERE other_product_id = '{$product_id}'
													ORDER BY  other_method_id DESC
													LIMIT 10  ";
        return $this->getRecordsetExtend($sql);
    }
    public function getOMoT_ShowEditPage($product_id) {
        $sql ="SELECT * FROM ams_other_transport_method WHERE other_product_id = '{$product_id}' AND 	other_title_of_method <> '' ";
        return $this->getRecordsetExtend($sql);
    }
    public function getParrentAttOfItemProduct($product_id) {
        $sql ="SELECT * FROM ams_product_attribute AS apa INNER JOIN attribute_detail AS ad ON (apa.atd_id = ad.atd_id)
					INNER JOIN attribute AS a ON (a.at_id = ad.at_id)
				 WHERE apa.product_id = '{$product_id}' ";
        //	echo $sql;
        return $this->getRecordsetExtend($sql);
    }
    public function getAllAttDet() {
        $sql ="SELECT * FROM attribute_detail WHERE atd_status = 1 ORDER BY atd_id DESC";
        return $this->getRecordsetExtend($sql);
    }
    public function getAllAttParDet($at_id) {
        $sql ="SELECT * FROM attribute_detail WHERE at_id = '{$at_id}' ORDER BY atd_id DESC";
        return $this->getRecordsetExtend($sql);
    }
    public function getAllAttParr() {
        $sql ="SELECT * FROM attribute WHERE at_status =1 ";
        return $this->getRecordsetExtend($sql);
    }

    public function updateAttribute($aData) {

    //step 1 delete old
        $check = true;
        $sqlDel = "DELETE FROM ams_product_attribute WHERE product_id = '{$aData['product_id']}' ";
        $checkF= $this->db_query($sqlDel);
        if ($checkF) {
        //Step 2 get variable name
        //	$arrVariable = $this->getAllAttParr();
        //		if (is_array($arrVariable))
        //		{
        //				foreach ($arrVariable as $keyCombi){
        //				 $variable = 'parrentAtribute139211'.$keyCombi['at_id'];
        //				 print_r($variable);
        //					if(isset($aData[$variable]))
        //					{
        //							//	step 3 sql upd
        //						$sqlIns	= "  INSERT INTO ams_product_attribute(product_id,atd_id)
        //										VALUES('{$aData['product_id']}','{$aData[$variable]}') ";
        //						echo $sqlIns;
        //						die();
        //						if(!$this->db_query($sqlIns))
        //						{$check =false; break;}
        //						}
        //					}
        //			}
            if (count($_REQUEST['hotel_page_sort'] > 0)) {

                foreach ($_REQUEST['hotel_page_sort'] as $sKey => $sValue) {

                    if(isset($_REQUEST['parent_att'][$sKey]) && $_REQUEST['parent_att'][$sKey]!='') {
                        $att=$sKey;
                        $sqlIns	= " INSERT INTO ams_product_attribute(product_id,atd_id)
									VALUES('{$aData['product_id']}','{$att}') ";

                        if(!$this->doUpdateExtend($sqlIns)) {
                            $check=false;break;
                        }
                    }
                }
            }

        }
        return $check;
    }
    public function saveAttribute($aData) {

    //Step 2 get variable name
    //$arrVariable = $this->getAllAttParr();
    //		if (is_array($arrVariable)) {
    //				foreach ($arrVariable as $keyCombi){
    //				 $variable = 'parrentAtribute139211'.$keyCombi['at_id'];
    //					if(isset($aData[$variable])){
    //							//	step 3 sql upd
    //						$sqlIns	= "  INSERT INTO ams_product_attribute(product_id,atd_id)
    //										VALUES('{$aData['product_id']}','{$aData[$variable]}') ";
    //						if(!$this->db_query($sqlIns)) {$check =false; break;}
    //						}
    //					}
    //			}

        if (count($_REQUEST['hotel_page_sort'] > 0)) {

            foreach ($_REQUEST['hotel_page_sort'] as $sKey => $sValue) {

                if(isset($_REQUEST['parent_att'][$sKey]) && $_REQUEST['parent_att'][$sKey]!='') {
                    $att=$sKey;
                    $sqlIns	= " INSERT INTO ams_product_attribute(product_id,atd_id)
									VALUES('{$aData['product_id']}','{$att}') ";

                    if(!$this->doUpdateExtend($sqlIns)) {
                        $check=false;break;
                    }
                }
            }
        }

        return $check;
    }
    //DELETE ATT WHEN DELETE PRODUCT
    public function deleteProAtt($oModel) {
        $pro_id=$oModel->getData('product_id');
        $sqlDel = "DELETE FROM ams_product_attribute WHERE product_id = '{$pro_id}' ";
        return $this->doUpdateExtend($sqlDel);
    }

    //GET ALL PARENT OF CATEGORY
    //STEP 1 : get parent of current cat
    public function getCatParent($cat_yahoo_id) {
        $sSql="SELECT * FROM ams_product_category WHERE product_category_yahoo_id = '{$cat_yahoo_id}'";
        return $this->getRecordsetByPKExtend($sSql);
    }

    public function getCatInfo($cat_id) {
        $sSql="SELECT * FROM ams_product_category WHERE product_category_id = {$cat_id}";
        return $this->getRecordsetByPKExtend($sSql);
    }

    /**
     * TEMPLATE
     */
    public function getListTemplate($oModel) {
        $sSql="SELECT * FROM ams_template WHERE status = 1 ORDER BY name";

        return $this->getRecordsetExtend($sSql);
    }

    public function getListSize() {
        $sSql="SELECT * FROM attribute WHERE at_status=1";
        return $this->getRecordsetExtend($sSql);
    }
    public function getSizeDetail($Id) {
        $sSql="SELECT * FROM attribute WHERE at_id = {$Id}";
        return $this->getRecordsetByPKExtend($sSql);
    }

    public function getListExportCsv($oModel,$condition="") {
        $fromDate = ($oModel->getData('fromDate'));
        $toDate = ($oModel->getData('toDate'));
        /**
         * ADD BY MINH FILTER BY CATEGORY
         */
        $category=$oModel->getData('category');
        $sFilterCat="";

        if(is_numeric($category)) {
            $sFilterCat = " AND product_category_id = {$category}";
        }
        else {
            $arr_cat_id=$this->getCategoryIdByName($category);
            if(is_array($arr_cat_id)) {
                $sFilterCat = " AND product_category_id in( ";
                $num=0;
                $lenght=count($arr_cat_id);
                foreach($arr_cat_id as $row) {
                    if($num==$lenght-1) {
                        $sFilterCat.= $row['product_category_yahoo_id'];
                    }
                    else {
                        $sFilterCat.= $row['product_category_yahoo_id'].',';
                    }
                    $num++;
                }
                $sFilterCat.= ")";
            }
        }
        if($fromDate!='' && $toDate!='') {
            $sFilterDate='';
            $sFilterDate.=	" AND ( date_format(p.product_date_added,'%Y-%m-%d') >= '{$fromDate}' ";
            $sFilterDate.=	" AND date_format(p.product_date_added,'%Y-%m-%d') <= '{$toDate}' ) ";
        }

        $sWhere=$sFilterCat.$sFilterDate;
        //BACK UP DATA
        if($condition!="") {
            $sSql="SELECT * FROM ams_product p WHERE 1 {$sWhere} AND product_id IN (".$condition.") ORDER BY p.product_date_added DESC";
        }
        else {
            $sSql="SELECT * FROM ams_product p WHERE 1 {$sWhere} ORDER BY p.product_date_added DESC";
        }
		echo $sSql;
        return $this->getRecordsetExtend($sSql);
    }

    public function backUpAfterExortCsv($oModel) {
        $fromDate = ($oModel->getData('fromDate'));
        $toDate = ($oModel->getData('toDate'));
        /**
         * ADD BY MINH FILTER BY CATEGORY
         */
        $category=$oModel->getData('category');
        $sFilterCat="";

        if(is_numeric($category)) {
            $sFilterCat = " AND product_category_id = {$category}";
        }
        else {
            $arr_cat_id=$this->getCategoryIdByName($category);
            if(is_array($arr_cat_id)) {
                $sFilterCat = " AND product_category_id in( ";
                $num=0;
                $lenght=count($arr_cat_id);
                foreach($arr_cat_id as $row) {
                    if($num==$lenght-1) {
                        $sFilterCat.= $row['product_category_yahoo_id'];
                    }
                    else {
                        $sFilterCat.= $row['product_category_yahoo_id'].',';
                    }
                    $num++;
                }
                $sFilterCat.= ")";
            }
        }
        if($fromDate!='' && $toDate!='') {
            $sFilterDate='';
            $sFilterDate.=	" AND ( date_format(p.product_date_added,'%Y-%m-%d') >= '{$fromDate}' ";
            $sFilterDate.=	" AND date_format(p.product_date_added,'%Y-%m-%d') <= '{$toDate}' ) ";
        }

        $sWhere=$sFilterCat.$sFilterDate;
        //BACK UP DATA
        $sSql_back_up="SELECT * FROM ams_product p WHERE 1 {$sWhere}";
        $Rs=$this->getRecordsetExtend($sSql_back_up);
        if(is_array($Rs)) {
        //INSERT TO BACK UP PRODUCT
            $sSql_insert_to_back_up="INSERT INTO ams_product_back_up {$sSql_back_up}";
            $check1=$this->doUpdateExtend($sSql_insert_to_back_up);
            //INSERT TO BACKUP other transfer method
            $num=0;
            $num_1=0;
            $list_pro_id="";
            $list_pro_id_for_del="";
            foreach($Rs as $row) {
            //GET ALL PRODUCT's other method
                $sSql_other_method=" SELECT * FROM ams_other_transport_method where other_product_id = {$row['product_id']}";
                $rs_other_method=$this->getRecordsetByPKExtend($sSql_other_method);
                //If product have other method ->insert to backup table,delete from main table
                if(is_array($rs_other_method)) {
                    $sSql_insert_method="INSERT INTO ams_other_transport_method_backup {$sSql_other_method}";
                    $check2=$this->doUpdateExtend($sSql_insert_method);
                    if($num==0) {
                        $list_pro_id.=$row['product_id'];
                    }
                    else
                        $list_pro_id.=",".$row['product_id'];
                }

                //GET LIST PRODUCT FOR DELETE
                if($num_1==0) {
                    $list_pro_id_for_del.=$row['product_id'];
                }
                else
                    $list_pro_id_for_del.=",".$row['product_id'];
                $num++;
                $num_1++;
            }
            //DELETE FROM other method table
            if($list_pro_id!='') {
                $sSql_delete_method="DELETE FROM ams_other_transport_method WHERE other_product_id in ({$list_pro_id})";
                $check3=$this->doUpdateExtend($sSql_delete_method);
            }
            //DELETE FROM PRODUCT
            $sSql= "DELETE FROM ams_product WHERE product_id in ({$list_pro_id_for_del})";
            $check4=$this->doUpdateExtend($sSql);
        }
        if($check1 && $check2 && $check3 && $check4) {
            return true;
        }
        else
            return false;
    }


}

